package com.borun.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.borun.bean.User;
import com.borun.bean.page.Page;
import com.borun.dao.base.Dao;
import com.borun.util.DBUtil;

public class UserDao implements Dao<User> {

	@Override
	public boolean add(User model) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "insert into t_user(username,password) values(?,?)";
		PreparedStatement psmt = conn.prepareStatement(sql);
		psmt.setString(1, model.getUsername());
		psmt.setString(2, model.getPassword());
		return psmt.executeUpdate() > 0;
	}

	@Override
	public boolean delete(Integer id) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "delete from t_user where id = ?";
		PreparedStatement psmt = conn.prepareStatement(sql);
		psmt.setInt(1, id);
		return psmt.executeUpdate() > 0;
	}

	@Override
	public boolean update(User model) throws SQLException {
		Connection conn = DBUtil.getConnection();
		PreparedStatement psmt = null;
		ArrayList<Object> map = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer("update t_user set ");
		if (model.getUsername() != null) {
			sql.append(" username = ?,");
			map.add(model.getUsername());
		}

		if (model.getPassword() != null) {
			sql.append(" password = ?, ");
			map.add(model.getPassword());
		}

		sql.delete(sql.length() - 1, sql.length());

		sql.append(" where id = ? ");
		map.add(model.getId());

		psmt = conn.prepareStatement(sql.toString());

		for (int i = 0; i < map.size(); i++) {
			psmt.setObject(i + 1, map.get(i));
		}
		return psmt.executeUpdate() > 0;
	}

	@Override
	public User findById(Integer id) throws SQLException {

		User user = null;
		Connection conn = DBUtil.getConnection();
		String sql = "select id,username,password from t_user where id = ?";
		PreparedStatement psmt = conn.prepareStatement(sql);
		psmt.setInt(1, id);
		ResultSet rs = psmt.executeQuery();
		while (rs.next()) {
			user = new User();
			user.setId(rs.getInt("id"));
			user.setUsername(rs.getString("username"));
			user.setPassword(rs.getString("password"));
		}
		return user;
	}

	@Override
	public User findBy(User model) throws SQLException {
		Connection conn = DBUtil.getConnection();
		User user = null;
		PreparedStatement psmt = null;
		ArrayList<Object> map = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer(
				"select top 1 id,username,password from t_user where 1 = 1 ");
		if (model.getUsername() != null) {
			sql.append(" and username like ? ");
			map.add("%" + model.getUsername() + "%");
		}

		if (model.getPassword() != null) {
			sql.append(" and password like ? ");
			map.add("%" + model.getPassword() + "%");
		}

		psmt = conn.prepareStatement(sql.toString());

		for (int i = 0; i < map.size(); i++) {
			psmt.setObject(i + 1, map.get(i));
		}

		ResultSet rs = psmt.executeQuery();
		while (rs.next()) {
			user = new User();
			user.setId(rs.getInt("id"));
			user.setUsername(rs.getString("username"));
			user.setPassword(rs.getString("password"));
		}
		return user;
	}

	@Override
	public List<User> getList() throws SQLException {
		Connection conn = DBUtil.getConnection();
		Statement stmt = null;
		String sql = "select id,username,password from t_user where 1 = 1 ";
		stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		List<User> userList = new ArrayList<User>();
		User user = null;
		while (rs.next()) { // 如果对象中有数据，就会循环打印出来
			user = new User();
			user.setId(rs.getInt("id"));
			user.setUsername(rs.getString("username"));
			user.setPassword(rs.getString("password"));
			userList.add(user);
		}
		return userList;
	}

	@Override
	public List<User> getListBy(User model) throws SQLException {
		Connection conn = DBUtil.getConnection();
		PreparedStatement psmt = null;
		ArrayList<Object> map = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer(
				"select id,username,password from t_user where 1 = 1 ");
		if (model.getUsername() != null) {
			sql.append(" and username like ? ");
			map.add("%" + model.getUsername() + "%");
		}

		if (model.getPassword() != null) {
			sql.append(" and password like ? ");
			map.add("%" + model.getPassword() + "%");
		}

		psmt = conn.prepareStatement(sql.toString());

		for (int i = 0; i < map.size(); i++) {
			psmt.setObject(i + 1, map.get(i));
		}

		ResultSet rs = psmt.executeQuery();
		List<User> userList = new ArrayList<User>();
		User user = null;
		while (rs.next()) { // 如果对象中有数据，就会循环打印出来
			user = new User();
			user.setId(rs.getInt("id"));
			user.setUsername(rs.getString("username"));
			user.setPassword(rs.getString("password"));
			userList.add(user);
		}
		return userList;
	}

	@Override
	public List<User> getPagedList(Page page) throws SQLException {
		Connection conn = DBUtil.getConnection();
		PreparedStatement psmt = null;
		String sql = "select id,username,password from t_user where 1 = 1 limit ?,? ";
		psmt = conn.prepareStatement(sql);
		psmt.setInt(1, page.getPageIndex());
		psmt.setInt(2, page.getPageSize());

		ResultSet rs = psmt.executeQuery();
		List<User> userList = new ArrayList<User>();
		User user = null;
		while (rs.next()) { // 如果对象中有数据，就会循环打印出来
			user = new User();
			user.setId(rs.getInt("id"));
			user.setUsername(rs.getString("username"));
			user.setPassword(rs.getString("password"));
			userList.add(user);
		}
		return userList;
	}

	@Override
	public List<User> getPagedListBy(Page page, User model) throws SQLException {
		Connection conn = DBUtil.getConnection();
		PreparedStatement psmt = null;
		ArrayList<Object> map = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer(
				"select id,username,password from t_user where 1 = 1 ");
		if (model.getUsername() != null) {
			sql.append(" and username like ? ");
			map.add("%" + model.getUsername() + "%");
		}

		if (model.getPassword() != null) {
			sql.append(" and password like ? ");
			map.add("%" + model.getPassword() + "%");
		}

		sql.append(" limit ?,? ");
		map.add(page.getPageIndex());
		map.add(page.getPageSize());

		psmt = conn.prepareStatement(sql.toString());

		for (int i = 0; i < map.size(); i++) {
			psmt.setObject(i + 1, map.get(i));
		}

		ResultSet rs = psmt.executeQuery();
		List<User> userList = new ArrayList<User>();
		User user = null;
		while (rs.next()) { // 如果对象中有数据，就会循环打印出来
			user = new User();
			user.setId(rs.getInt("id"));
			user.setUsername(rs.getString("username"));
			user.setPassword(rs.getString("password"));
			userList.add(user);
		}
		return userList;
	}

}
